package tech.wangtong.dao.impl;

import tech.wangtong.dao.UserDao;
import tech.wangtong.pojo.User;
import tech.wangtong.util.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

public class UserDaoImpl implements UserDao{
    public int update(User u) {
        String sql="update myuser set password=?,sex=?,status=?,headPic=? where id=?";
        return DBUtil.update(sql,u.getPassword(),u.getSex(),u.getStatus(),u.getHeadPic(),u.getId());
    }

    @Override
    public int selectCount() {
        String sql = "select count(*) from myuser";
        ResultSet rs = DBUtil.select(sql);
        try {
            while (rs.next()) return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, DBUtil.pstmt, DBUtil.conn);
        }
        return 0;
    }

    @Override
    public List<User> selectPage(Integer pageNum, Integer pageSize) {
        String sql="select * from myuser limit ?,?";
        ResultSet rs=DBUtil.select(sql,(pageNum-1)*pageSize,pageSize);
        List<User> list=new ArrayList<>();
        try {
            while(rs.next()) list.add(checkResultSet(rs));
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, DBUtil.pstmt, DBUtil.conn);
        }
        return list;
    }

    @Override
    public boolean checkUsername(String username) {
        String sql="select count(*) from myuser where name=?";
        ResultSet rs=DBUtil.select(sql,username);
        try {
            while(rs.next()) return rs.getInt(1)>0;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs, DBUtil.pstmt, DBUtil.conn);
        }
        return false;
    }

    public User selectById(Integer id) {
        String sql="select * from myuser where id=?";
        ResultSet rs=DBUtil.select(sql,id);
        User u=null;
        try {
            while(rs.next()){
                u=checkResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,DBUtil.pstmt,DBUtil.conn);
        }
        return u;
    }

    @Override
    public int add(User u) {
        String sql="insert into myuser values(null,?,?,?,?,?,?)";
        return DBUtil.update(sql,u.getName(),u.getPassword(),
                u.getSex(),u.getStatus(),u.getTime(),u.getHeadPic());
    }

    @Override
    public List<User> show() {
        String sql="select * from myuser";
        ResultSet rs=DBUtil.select(sql);
        List<User> list=new ArrayList<>();
        try {
            while(rs.next()) list.add(checkResultSet(rs));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,DBUtil.pstmt,DBUtil.conn);
        }
        return list;
    }

    @Override
    public User login(String username, String password) {
        String sql="select * from myuser where name=? and password=?";
        ResultSet rs=DBUtil.select(sql,username,password);
        User u=null;
        try {
            while(rs.next()) u=checkResultSet(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,DBUtil.pstmt,DBUtil.conn);
        }
        return u;
    }

    @Override
    public int updatePassword(User u) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        String sql="delete from myuser where id=?";
        return DBUtil.update(sql,id);
    }

    //处理结果集的方法
    public User checkResultSet(ResultSet rs) throws SQLException {
        Integer id=rs.getInt("id");
        String un=rs.getString("name");
        String pw=rs.getString("password");
        Integer sex=rs.getInt("sex");
        Integer status=rs.getInt("status");
        Date time=rs.getDate("time");
        String headPic = rs.getString("headPic");
        return new User(id,un,pw,sex,status,time,headPic);
    }
}
